﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using DBUtil.Entity;

namespace DBUtil
{
    internal class DBInfo
    {
        internal DB Db = null;
        internal DBInfo(DB db)
        {
            this.Db = db;
        }

        /// <summary>
        /// 获取当前数据库的所有表名
        /// </summary>
        internal List<string> TableNames
        {
            get
            {
                string strSql = string.Empty;
                switch (Db.DBaseType)
                {
                    case DataBaseType.MsSql:
                        strSql = "select [name] from sysobjects where xtype='U'and [name]<>'dtproperties' order by [name]";
                        break;
                    case DataBaseType.MySql:
                        strSql = "SELECT table_name FROM information_schema.tables WHERE table_schema='" + Db.DBConn.Database + "' and table_type='base table' ORDER BY table_name asc;";
                        break;
                    case DataBaseType.Oracle:
                    case DataBaseType.OracleManaged:
                        strSql = "select table_name from user_tables Order By table_name";
                        break;
                    case DataBaseType.Sqlite:
                        strSql = "SELECT name FROM sqlite_master WHERE type='table' order by name";
                        break;
                    default:
                        throw new NotImplementedException("未知数据库类型！");
                }
                return Db.QueryTable(strSql,300).GetFirstCol<string>();
            }
        }

        /// <summary>
        /// 获取当前表的所有列信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        internal List<ColumnInfo> GetAllColumnInfo(string tableName)
        {
            string strSql = string.Empty;
            List<ColumnInfo> lstCols = new List<ColumnInfo>();
            switch (Db.DBaseType)
            {
                case DataBaseType.MySql:
                    strSql = "SHOW FULL COLUMNS FROM " + tableName;
                    lstCols = MySqlReadColInfo(strSql);
                    break;
                case DataBaseType.MsSql:
                    strSql = "SELECT distinct * from (select colorder=C.column_id,ColumnName=C.name,deText=ISNULL(PFD.[value],N''),TypeName=T.name, Length=C.max_length, Preci=C.precision, Scale=C.scale, IdentityMark=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,PKMark=ISNULL(IDX.PrimaryKey,N''),Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END, IndexName=ISNULL(IDX.IndexName,N''), IndexSort=ISNULL(IDX.Sort,N''), Create_Date=O.Create_Date, Modify_Date=O.Modify_date, CanNullMark=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END, defaultVal=ISNULL(D.definition,N'') FROM sys.columns C INNER JOIN sys.objects O ON C.[object_id]=O.[object_id] AND (O.type='U' or O.type='V') AND O.is_ms_shipped=0 INNER JOIN sys.types T ON C.user_type_id=T.user_type_id LEFT JOIN sys.default_constraints D ON C.[object_id]=D.parent_object_id AND C.column_id=D.parent_column_id AND C.default_object_id=D.[object_id] LEFT JOIN sys.extended_properties PFD ON PFD.class=1  AND C.[object_id]=PFD.major_id  AND C.column_id=PFD.minor_id LEFT JOIN sys.extended_properties PTB ON PTB.class=1 AND PTB.minor_id=0  AND C.[object_id]=PTB.major_id LEFT JOIN ( SELECT  IDXC.[object_id], IDXC.column_id, Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END, IndexName=IDX.Name FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id LEFT JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id INNER JOIN  ( SELECT [object_id], Column_id, index_id=MIN(index_id) FROM sys.index_columns GROUP BY [object_id], Column_id ) IDXCUQ ON IDXC.[object_id]=IDXCUQ.[object_id] AND IDXC.Column_id=IDXCUQ.Column_id AND IDXC.index_id=IDXCUQ.index_id ) IDX ON C.[object_id]=IDX.[object_id] AND C.column_id=IDX.column_id  WHERE O.name=N'" + tableName + "' ) as t ORDER BY colorder,ColumnName  ";
                    lstCols = Db.QueryTable(strSql).ConvertToListObject<ColumnInfo>();
                    break;
                case DataBaseType.Oracle:
                case DataBaseType.OracleManaged:
                    strSql = "";
                    break;
                case DataBaseType.Sqlite:
                    lstCols = SqliteColInfo(tableName);
                    break;
                default:
                    throw new NotImplementedException("未知数据库类型！");
            }
            return lstCols;
        }

        #region MySql 获取列信息
        private List<ColumnInfo> MySqlReadColInfo(string strSql)
        {
            List<ColumnInfo> lstCols = new List<ColumnInfo>();
            DbDataReader reader = null;
            try
            {
                reader = Db.ExecuteReader(strSql);
                int colorder = 1;
                while (reader.Read())
                {
                    ColumnInfo colInfo = new ColumnInfo();
                    colInfo.Colorder = colorder;

                    if ((!Object.Equals(reader["Field"], null)) && (!Object.Equals(reader["Field"], System.DBNull.Value)))
                    {
                        colInfo.ColumnName = reader["Field"].ToString();
                    }

                    if ((!Object.Equals(reader["Type"], null)) && (!Object.Equals(reader["Type"], System.DBNull.Value)))
                    {
                        string typename = reader["Type"].ToString();
                        string len = "", pre = "", scal = "";
                        TypeNameProcess(typename, out typename, out len, out pre, out scal);

                        colInfo.TypeName = typename;
                        colInfo.Length = len.ConvertTo<int?>(null);
                        colInfo.Preci = pre.ConvertTo<int?>(null);
                        colInfo.Scale = scal.ConvertTo<int?>(null);
                    }

                    if ((!Object.Equals(reader["Key"], null)) && (!Object.Equals(reader["Key"], System.DBNull.Value)))
                    {
                        string skey = reader["Key"].ToString();
                        colInfo.PKMark = (skey.Trim() == "PRI") ? "√" : "";
                    }

                    if ((!Object.Equals(reader["Null"], null)) && (!Object.Equals(reader["Null"], System.DBNull.Value)))
                    {
                        string snull = reader["Null"].ToString();
                        colInfo.CanNullMark = (snull.Trim() == "YES") ? "√" : "";
                    }
                    if ((!Object.Equals(reader["Default"], null)) && (!Object.Equals(reader["Default"], System.DBNull.Value)))
                    {
                        string defaultVal = reader["Default"].ToString();
                        colInfo.DefaultVal = defaultVal;
                    }

                    if ((!Object.Equals(reader["Comment"], null)) && (!Object.Equals(reader["Comment"], System.DBNull.Value)))
                    {
                        string deText = reader["Comment"].ToString();
                        colInfo.DeText = deText;
                    }
                    if ((!Object.Equals(reader["Extra"], null)) && (!Object.Equals(reader["Extra"], System.DBNull.Value)))
                    {
                        string extra = reader["Extra"].ToString();
                        colInfo.IdentityMark = (extra.Trim() == "auto_increment") ? "√" : "";
                    }
                    lstCols.Add(colInfo);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
            }
            return lstCols;
        }

        //对类型名称 解析
        private void TypeNameProcess(string strName, out string TypeName, out string Length, out string Preci, out string Scale)
        {
            TypeName = strName;
            int n = strName.IndexOf("(");
            Length = "";
            Preci = "";
            Scale = "";
            if (n > 0)
            {
                TypeName = strName.Substring(0, n);
                switch (TypeName.Trim().ToUpper())
                {
                    //只有大小(M)
                    case "TINYINT":
                    case "SMALLINT":
                    case "MEDIUMINT":
                    case "INT":
                    case "INTEGER":
                    case "BIGINT":
                    case "TIMESTAMP":
                    case "CHAR":
                    case "VARCHAR":
                        {
                            int m = strName.IndexOf(")");
                            Length = strName.Substring(n + 1, m - n - 1);
                        }
                        break;
                    case "FLOAT"://(M,D)
                    case "DOUBLE":
                    case "REAL":
                    case "DECIMAL":
                    case "DEC":
                    case "NUMERIC":
                        {
                            int m = strName.IndexOf(")");
                            string strlen = strName.Substring(n + 1, m - n - 1);
                            int i = strlen.IndexOf(",");
                            Length = strlen.Substring(0, i);
                            Scale = strlen.Substring(i + 1);
                        }
                        break;
                    case "ENUM"://(M1,M2,M3)
                    case "SET":
                        {
                        }
                        break;
                    default:
                        break;
                }
            }

        }
        #endregion

        #region Sqlite获取列信息
        private List<ColumnInfo> SqliteColInfo(string tableName)
        {
            List<ColumnInfo> lstCols = new List<ColumnInfo>();
            var dbConn = Db.DBConn;
            dbConn.Open();
            DataRow[] columns = dbConn.GetSchema("COLUMNS").Select("TABLE_NAME='" + tableName + "'");
            foreach (DataRow dr in columns)
            {
                ColumnInfo colInfo = new ColumnInfo();
                colInfo.Colorder = dr["ORDINAL_POSITION"].ToString().ConvertTo<int>(0);
                colInfo.ColumnName = dr["COLUMN_NAME"].ToString();
                colInfo.Length = dr["CHARACTER_MAXIMUM_LENGTH"].ToString().ConvertTo<int?>(null);
                colInfo.Preci = dr["NUMERIC_PRECISION"].ToString().ConvertTo<int?>(null);
                colInfo.Scale = dr["NUMERIC_SCALE"].ToString().ConvertTo<int?>(null);
                colInfo.PKMark = dr["PRIMARY_KEY"].ToString().ToLower() == "true" ? "√" : "";
                colInfo.CanNullMark = dr["IS_NULLABLE"].ToString().ToLower() == "true" ? "√" : "";
                colInfo.DefaultVal = dr["COLUMN_DEFAULT"].ToString();
                colInfo.TypeName = dr["DATA_TYPE"].ToString();
                if (colInfo.PKMark == "√" && string.Compare(colInfo.TypeName, "integer", true) == 0)
                {
                    colInfo.IdentityMark = "√";
                }
                lstCols.Add(colInfo);
            }
            dbConn.Close();
            return lstCols;
        }
        #endregion
    }
}

